
IF OBJECT_ID('dbo.RptSubContractActivityDetailReport') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptSubContractActivityDetailReport
    IF OBJECT_ID('dbo.RptSubContractActivityDetailReport') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptSubContractActivityDetailReport >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptSubContractActivityDetailReport >>>'
END
go                                            
-- =======================================================================  
-- Procedure Name: dbo.RptSubContractActivityDetailReport  
--  
-- Author:      Kevin Cashman, Parallax Consulting LLC   
--  
-- Create date: 02/26/2009  
--  
-- Description: Gets Raw data of SubDeliverables  
--  
-- Serves as a drill down to the Report, RptSubContractActivityReport  
--  
-- Parameters:    
--     @StartDate                           
--     @EndDate                             
--     @DeliverableStatus                   
--     @Institution                         
--     @Department                          
--     @Unit    
--     @ChiefCode                 
--     @FundNumber               
--     @PrincipalInvestigator     
--     @OriginatingSponsor  
--     @Sponsor                                      
--     @Subcontractor                       
--     @SubcontractSpecialist               
--     @DomainUserId                        
--  
-- Returns:     INT, 0=Success, -1=Error  
--  
-- SampleCall:  
-- RptSubContractActivityDetailReport '03/08/2009','03/15/2009',NULL,'Sub In - Assigned, Sub Out - Partially Executed,Sub In - Partially Executed',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'PartiallyExecuted',11055   
-- EXECUTE dbo.RptSubContractActivityDetailReport(  
--                       @StartDate               = '6/1/2008',  
--                       @EndDate                 = '8/31/2008',  
--                       @DeliverableType         = NULL,  
--                       @DeliverableStatus       = NULL,   
--                       @Institution             = NULL,   
--                       @Department              = NULL,   
--                       @Unit                    = NULL,   
--                       @ChiefCode               = NULL,  
--    @FundNumber              = NULL,  
--    @PrincipalInvestigator   = NULL,   
--    @OriginatingSponsor      = NULL,  
--    @Sponsor                 = NULL,  
--                       @Subcontractor           = NULL,   
--                       @SubcontractSpecialist   = NULL,  
--    @SelectedHeaderStatus   = NULL,   
--                       @DomainUserId            = 11055 )  
--  
--------------------------------------------------------------------------  
-- Date        Initials  Modification  
--------------------------------------------------------------------------  
-- 02/26/09    RM    Created based on existing RptSubContractDeliverableDetailReport  
-- 03/12/09    RM  Added Deliverable Status back in as a parameter  
-- 03/16/09    RM  Fixed stored procedure to work with multiple deliverable statuses and to return error when invalid value is passed for status  
-- 03/17/09    RM  Add the SelectedHeaderStatus parameter to capture the column selected by the user  
-- 03/18/09    RM  Change result set to return ALL if "All" is anywhere in the deliverable status string  
-- 09/08/09    PC    Few deliverable statuses removed - Trackit 9868  
-- =======================================================================  

--EXEC RptSubContractActivityDetailReport '03/13/2009','03/13/2009',NULL,'ALL',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,11055   

CREATE PROCEDURE [dbo].[RptSubContractActivityDetailReport]    
     (    
     @StartDate                         DATETIME,      
     @EndDate                           DATETIME,    
     @DeliverableType                   VARCHAR(200) = NULL,    
     @DeliverableStatus                 VARCHAR(5000) = NULL,    
     @Institution                       VARCHAR(100) = NULL,    
     @Department                        VARCHAR(100) = NULL,    
     @Unit                              VARCHAR(100) = NULL,    
     @ChiefCode                         VARCHAR(4)   = NULL,    
     @FundNumber                        VARCHAR(1000) = NULL,    
     @PrincipalInvestigator             VARCHAR(100) = NULL,     
     @OriginatingSponsor                VARCHAR(100) = NULL,    
     @Sponsor                           VARCHAR(100) = NULL,    
     @Subcontractor                     VARCHAR(200) = NULL,    
     @SubcontractSpecialist             VARCHAR(100) = NULL,    
     @SelectedHeaderStatus  VARCHAR(1000)= NULL,    
	 @ContractManager                   VARCHAR(100) = NULL,
     @DomainUserId                      INT    
     )     
WITH RECOMPILE                                              
AS    
BEGIN    
     -- Initial Settings    
     SET NOCOUNT ON    
         
     -- Declare Local Variables    
     DECLARE   @Error              INT,    
               @RowCount           INT,     
        @DeliverableStatusCodes VARCHAR(5000)    
    
     -- Need to break the filter out to multiple steps, so need a temp table here    
     DECLARE   @Subcontracts TABLE (    
                    FolderNumber             VARCHAR(12)    NULL,    
                    InfoEdPropNumber         VARCHAR(50)    NULL,    
                    Institution              VARCHAR(50)    NULL,    
                    Department               VARCHAR(50)    NULL,    
                    Unit                     VARCHAR(50)    NULL,    
                    ChiefCode                VARCHAR(4)     NULL,    
                    SponsorName              VARCHAR(75)    NULL,    
                    SponsorTypeName          VARCHAR(50)    NULL,    
                    SecondarySponsor         VARCHAR(75)    NULL,    
                    ProposalShortTitle       VARCHAR(64)    NULL,    
                    PrincipalInvestigator    VARCHAR(64)    NULL,    
                    ProjectStartDate         DATETIME       NULL,    
                    ProjectEndDate           DATETIME       NULL,    
                    InstrumentType           VARCHAR(50)    NULL,    
                    SubContractorName        VARCHAR(75)    NULL,    
                    BudgetProjectStartDate   DATETIME       NULL,    
                    BudgetProjectEndDate     DATETIME       NULL,    
                    ProjectAwardedAmount     MONEY          NULL,    
                    BudgetStartDate          DATETIME       NULL,    
                    BudgetEndDate            DATETIME       NULL,    
                    BudgetFundNumber         VARCHAR(20)    NULL,    
                    ExpenseAmount            MONEY          NULL,    
                    IndirectExpenses         MONEY          NULL,    
                    DirectExpenses           MONEY          NULL,    
                    CashBalance              MONEY          NULL,    
                    ReportId                 BIGINT         NULL,    
                    ReportName               VARCHAR(50)    NULL,    
                    REPORTCAT                VARCHAR(80)    NULL,    
                    REPORTSTAT               VARCHAR(80)    NULL,    
                    SubmittedDate            DATETIME       NULL,    
                    DueDate                  DATETIME       NULL,    
                    StatusDate               DATETIME       NULL,    
                    CompletedDate            DATETIME       NULL,    
                    DaysFromDueToCompleted   VARCHAR(5)     NULL,    
                    SubmittedBy              VARCHAR(50)    NULL,    
                    StatusBy                 VARCHAR(50)    NULL,    
                    CompletedBy              VARCHAR(50)    NULL,    
                    PeriodStartDate          DATETIME       NULL,    
                    PeriodEndDate            DATETIME       NULL,    
                    DeliverableComments      VARCHAR(2000)  NULL,    
                    REPORTCAT_CODE           VARCHAR(50)    NULL,    
                    REPORTSTAT_CODE          VARCHAR(50)    NULL,    
                    AgreementId              INT            NULL,    
                    InstitutionId            INT            NULL,    
                    DepartmentId             INT            NULL,    
                    UnitId                   INT            NULL,    
                    SponsorId                INT            NULL,    
                    PrincipalInvestigatorId  INT            NULL,    
                    SubcontractsYN           CHAR(1)        NULL,    
                    SubContractorId          INT            NULL,    
                    SubOutContractorId       INT            NULL,
					ContractSpecialistID	 VARCHAR(100)   NULL,
                    ContractSpecialist		 VARCHAR(60)	NULL)    
    
     -- ALL checking    
     IF isNull(@DeliverableStatus,'All') IN ('ALL','','0') SET @DeliverableStatus = 'All'    
     IF @Institution IN ('ALL','','0') SET @Institution = NULL    
     IF @Department IN ('ALL','','0') SET @Department = NULL    
     IF @Unit IN ('ALL','','0') SET @Unit = NULL    
     IF @ChiefCode IN ('ALL','','0') SET @ChiefCode = NULL    
     IF @FundNumber IN ('ALL','','0') SET @FundNumber = NULL    
     IF @PrincipalInvestigator IN ('ALL','','0') SET @PrincipalInvestigator = NULL    
     IF @OriginatingSponsor IN ('ALL','','0') SET @OriginatingSponsor = NULL    
     IF @Sponsor IN ('ALL','','0') SET @Sponsor = NULL    
     IF @Subcontractor IN ('ALL','','0') SET @Subcontractor = NULL    
     IF @SubcontractSpecialist IN ('ALL','','0') SET @SubcontractSpecialist = NULL    
	 IF @ContractManager IN ('ALL','','0') SET @ContractManager = NULL
         
     -- Verify firt.    
     IF @DeliverableType IS NOT NULL AND @DeliverableType NOT IN ('ALL','AllSubsIn','AllSubsOut','Only-SubIn','Only-SubOut','SubIn-MOD','SubOut-MOD','PSA-IN','PSA-OUT')    
     BEGIN    
          RAISERROR ('ERROR! DeliverableType has an invalid value(%s). Set to a valid DeliverableType and try again.',11,1,@DeliverableType)     
          RETURN -1    
     END    
                   
     -- Set Deliverable Type    
     IF @DeliverableType IS NOT NULL    
          SELECT @DeliverableType = CASE @DeliverableType     
                              WHEN 'AllSubsIn' THEN 'REPORTCAT_13,REPORTCAT_40,REPORTCAT_43'    
                              WHEN 'AllSubsOut' THEN 'REPORTCAT_14,REPORTCAT_39,REPORTCAT_44'    
                              WHEN 'Only-SubIn' THEN 'REPORTCAT_13'             
                              WHEN 'Only-SubOut' THEN 'REPORTCAT_14'            
                              WHEN 'SubIn-MOD' THEN 'REPORTCAT_43'    
                              WHEN 'SubOut-MOD' THEN 'REPORTCAT_44'    
                              WHEN 'PSA-IN' THEN 'REPORTCAT_40'    
                              WHEN 'PSA-OUT' THEN 'REPORTCAT_39'    
                              ELSE 'REPORTCAT_13,REPORTCAT_40,REPORTCAT_14,REPORTCAT_39,REPORTCAT_43,REPORTCAT_44'   -- ALL    
                           END    
    
 -- Check to see if All is anywhere in the status string    
 IF @DeliverableStatus like '%,All%' OR @DeliverableStatus like '%All,%'    
 BEGIN    
  SELECT @DeliverableStatus = 'All'    
 END    
    
      -- If drilling down to a single status, set the codes here    
 SELECT    @DeliverableStatusCodes = 'ERROR'    
    
  If @SelectedHeaderStatus is NULL or @SelectedHeaderStatus = 'UnExecutedAgreementRecd'  --'REPORTSTAT_40','REPORTSTAT_41'        
  BEGIN    
  SELECT    @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub In - Unexecuted Agreement Received%' or @DeliverableStatus = 'All' THEN @DeliverableStatusCodes + ',REPORTSTAT_40' ELSE @DeliverableStatusCodes END    
     END       
    

  If @SelectedHeaderStatus is NULL or @SelectedHeaderStatus = 'PartiallyExecuted'  --'REPORTSTAT_21','REPORTSTAT_30'       
  BEGIN    
   SELECT    @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub In - Partially Executed%' or @DeliverableStatus = 'All' THEN @DeliverableStatusCodes + ',REPORTSTAT_21' ELSE @DeliverableStatusCodes END    
  SELECT    @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub Out - Partially Executed%' or @DeliverableStatus = 'All' THEN @DeliverableStatusCodes + ',REPORTSTAT_30' ELSE @DeliverableStatusCodes END    
  END     
    
  If @SelectedHeaderStatus is NULL or @SelectedHeaderStatus = 'FullyExecuted'  --'REPORTSTAT_22','REPORTSTAT_31'      
  BEGIN    
   SELECT    @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub In - Fully Executed%' or @DeliverableStatus = 'All' THEN @DeliverableStatusCodes + ',REPORTSTAT_22' ELSE @DeliverableStatusCodes END    
  SELECT    @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub Out - Fully Executed%' or @DeliverableStatus = 'All' THEN @DeliverableStatusCodes + ',REPORTSTAT_31' ELSE @DeliverableStatusCodes END    
  END    
    
 If @SelectedHeaderStatus is NULL or @SelectedHeaderStatus = 'Requested'  --'REPORTSTAT_22','REPORTSTAT_31'      
  BEGIN    
   SELECT    @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub Out - Requested%' or @DeliverableStatus = 'All' THEN @DeliverableStatusCodes + ',REPORTSTAT_46' ELSE @DeliverableStatusCodes END    
  
  END    
    
      
  If (@SelectedHeaderStatus is NULL AND @DeliverableStatus= 'ALL')      
  BEGIN     
   SELECT    @DeliverableStatusCodes = CASE WHEN isNull(@DeliverableStatus,'All') = 'All' THEN  'REPORTSTAT_21,REPORTSTAT_30,REPORTSTAT_22,REPORTSTAT_31,REPORTSTAT_40,REPORTSTAT_46'    
   ELSE @DeliverableStatusCodes END    
  END    
    
     --Remove error string if other values were added to string    
     SELECT @DeliverableStatusCodes = replace(@DeliverableStatusCodes,'ERROR,','')    
       
     --Check for Errors    
     IF @DeliverableStatusCodes = 'ERROR' and isNull(@DeliverableStatus,'All') <> 'All'    
     BEGIN    
          RAISERROR ('ERROR! DeliverableStatus has an invalid value. Set to a valid StatusColumn name.',11,1,@DeliverableStatus)     
          RETURN -1    
     END    
    
     -- Performance Issues, Get Secured AgreementIds into a temp table    
     CREATE TABLE   #tt_Agreements  (AgreementId INT)    
         
     INSERT    #tt_Agreements (AgreementId)    
     SELECT    AgreementId    
     FROM      dbo.fnGetSecurityAgreements(@DomainUserId)   
  
 -- Added by Hitesh to test the performance of the reports  
 CREATE CLUSTERED INDEX IC_#tt_Agreements ON #tt_Agreements  
 (  
  AgreementId ASC  
 )  
   
 ---------    
         
     SELECT  ReportId, MIN(ProcessedDate) AS LastDate, REPORTSTAT    
     INTO #tmpSubContractDeliverables    
     FROM    dbo.vwInfoEdSubContractDeliverables     
     GROUP BY ReportId, REPORTSTAT    
  
 -- Added by Hitesh to test the performance of the reports  
 CREATE CLUSTERED INDEX IC_#tmpSubContractDeliverables ON #tmpSubContractDeliverables  
 (  
  ReportId ASC,  
  LastDate ASC,  
  REPORTSTAT ASC  
 )  
   
 ---------    
  
  
 SELECT D.prop_no,    
            D.ReportId,    
            D.ReportName,    
            D.REPORTCAT_CODE,    
            D.REPORTCAT,    
            D.REPORTSTAT,    
            D.REPORTSTAT_CODE,    
            D.DueDate,    
            D.SubmittedDate,    
            D.ProcessedDate AS StatusDate,    
            D.CompletedDate,    
            D.SubmittedBy,    
            D.StatusBy,    
            D.CompletedBy,    
            D.PeriodStartDate,    
            D.PeriodEndDate,    
            D.Comments,    
            D.DaysComplCalc    
 INTO #tmpDEL  
    FROM    dbo.vwInfoEdSubContractDeliverables D    
    INNER JOIN (SELECT  ReportId, LastDate, REPORTSTAT    
                FROM    dbo.#tmpSubContractDeliverables) LAST   
  ON D.ReportId = LAST.ReportId AND D.ProcessedDate = LAST.LastDate AND D.REPORTSTAT = LAST.REPORTSTAT  
  
  
 UPDATE #tmpDEL  
 SET StatusDate = CONVERT(DATETIME,CONVERT(VARCHAR(12),StatusDate))  
    
     -- Result Set into temp table    
     INSERT    @Subcontracts    
                    (FolderNumber,    
                    InfoEdPropNumber,    
                    Institution,    
                    Department,    
                    Unit,    
                    ChiefCode,    
                    SponsorName,    
                    SponsorTypeName,    
                    SecondarySponsor,    
                    ProposalShortTitle,    
                    PrincipalInvestigator,    
                    ProjectStartDate,    
                    ProjectEndDate,    
                    InstrumentType,    
                    SubContractorName,    
                    BudgetProjectStartDate,    
                    BudgetProjectEndDate,    
                    ProjectAwardedAmount,    
                    BudgetStartDate,    
                    BudgetEndDate,    
                    BudgetFundNumber,    
                    ExpenseAmount,    
                    IndirectExpenses,    
                    DirectExpenses,    
                    CashBalance,    
                    ReportId,    
                    ReportName,    
                    REPORTCAT,    
                    REPORTSTAT,    
                    SubmittedDate,    
                    DueDate,    
                    StatusDate,    
                    CompletedDate,    
                    DaysFromDueToCompleted,    
                    SubmittedBy,    
                    StatusBy,    
                    CompletedBy,    
                    PeriodStartDate,    
                    PeriodEndDate,    
                    DeliverableComments,    
                    REPORTCAT_CODE,    
                    REPORTSTAT_CODE,    
                    AgreementId,    
                    InstitutionId,    
                    DepartmentId,    
                    UnitId,    
                    SponsorId,    
                    PrincipalInvestigatorId,    
                    SubcontractsYN,    
                    SubContractorId,    
                    SubOutContractorId,
					ContractSpecialistID,
                    ContractSpecialist)
     SELECT    AI.FolderNumber,    
               AI.InfoEdPropNumber,    
               AI.Institution,    
               AI.Department,    
               AI.Unit,    
               AI.ChiefCode,    
               AI.SponsorName,    
               AI.SponsorTypeName,    
               AI.SecondarySponsor,    
               AI.ProposalShortTitle,    
               AI.PrincipalInvestigator,    
               AI.ProjectStartDate,    
               AI.ProjectEndDate,    
               AI.InstrumentType,                                    
               CASE WHEN DEL.REPORTCAT_CODE IN ('REPORTCAT_13','REPORTCAT_40','REPORTCAT_43')     
                    THEN 'N/A'                -- In    
                    ELSE dbo.fnParseSubcontractorName(DEL.ReportName)          -- Out    
               END AS SubContractorName,    
               BUDG.ProjectStartDate AS BudgetProjectStartDate,    
               BUDG.ProjectEndDate AS BudgetProjectEndDate,    
               BUDG.ProjectAwardedAmount,    
               BUDG.BudgetStartDate,    
               BUDG.BudgetEndDate,    
               BUDG.FundNumber AS BudgetFundNumber,    
               BUDG.ExpenseAmount,    
               BUDG.IndirectExpenses,    
               BUDG.DirectExpenses,    
               -1 * isNull(BUDG.CashBalance,0),    
               DEL.ReportId,    
               DEL.ReportName,    
               DEL.REPORTCAT,    
               DEL.REPORTSTAT,    
               DEL.SubmittedDate,    
               DEL.DueDate,    
               DEL.StatusDate,    
               DEL.CompletedDate,    
               DEL.DaysComplCalc AS DaysFromDueToCompleted,    
               ISNULL(DEL.SubmittedBy,'UNASSIGNED') AS Submittedby,    
               DEL.StatusBy,    
               DEL.CompletedBy,    
               DEL.PeriodStartDate,    
               DEL.PeriodEndDate,    
               DEL.Comments AS DeliverableComments,    
               DEL.REPORTCAT_CODE,    
               DEL.REPORTSTAT_CODE,    
               AI.AgreementId,    
               AI.InstitutionId,    
               AI.DepartmentId,    
               AI.UnitId,    
               AI.SponsorId,    
               AI.PrincipalInvestigatorId,    
               AI.SubcontractsYN,    
               AI.SubContractorId,    
               NULL,
			   IP.ContractSpecialistID,
               IP.ContractSpecialist     
                   
     FROM      dbo.AgrAgreement_Info AI     
                   
               -- Security Issues: Not all Users can see all Agreements!    
               INNER JOIN   #tt_Agreements AUTH ON AI.AgreementId = AUTH.AgreementId    
                        
               INNER JOIN  #tmpDel DEL ON AI.InfoEdPropNumber = DEL.prop_no    
			   INNER JOIN InfoEdUDFProposal IP ON AI.InfoEdPropNumber = IP.PropNumber
             
               LEFT OUTER JOIN    
                    -- This function uses the Agreement_Fct table to get Budget and Project data    
                    dbo.fnGetFactTableBudget(@StartDate, @EndDate) BUDG ON AI.AgreementId = BUDG.AgreementId    
    
     WHERE       
--    (dbo.fnRemoveTime(DEL.StatusDate) BETWEEN @StartDate AND @EndDate) AND   
  
               (DEL.StatusDate BETWEEN @StartDate AND @EndDate) AND   
               (@DeliverableType IS NULL OR DEL.REPORTCAT_CODE IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@DeliverableType))) AND    
               (DEL.REPORTSTAT_CODE IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@DeliverableStatusCodes))) AND    
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND    
               (@Department IS NULL OR ISNULL(AI.DepartmentId,-1) IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND    
               (@Unit IS NULL OR AI.UnitId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Unit))) AND    
               (@ChiefCode IS NULL OR AI.ChiefCode IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@ChiefCode))) AND    
               (@PrincipalInvestigator IS NULL OR AI.PrincipalInvestigatorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PrincipalInvestigator ))) AND       
               (@Sponsor IS NULL OR AI.SponsorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @Sponsor )))     
                   
     ORDER BY  AI.Institution,    
               AI.Department,    
               AI.Unit,    
               AI.FolderNumber,    
               DEL.StatusDate    
                   
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT    
     IF @Error!=0    
     BEGIN    
          RAISERROR ('ERROR! Getting Intial Agreement list into TempTable, @Subcontracts',11,1)    
          DROP TABLE #tt_Agreements    
          RETURN -1    
     END    
         
     -- Even on Success. Drop now.    
     DROP TABLE #tt_Agreements    
         
     -- Return Result Set using additional params    
     SELECT    FolderNumber,    
               InfoEdPropNumber,    
               Institution,    
               Department,    
               Unit,    
               ChiefCode,    
               SponsorName,    
               SponsorTypeName,    
               SecondarySponsor,    
               ProposalShortTitle,    
               PrincipalInvestigator,    
               ProjectStartDate,    
               ProjectEndDate,    
               InstrumentType,    
               SubContractorName,    
               BudgetProjectStartDate,    
               BudgetProjectEndDate,    
               ProjectAwardedAmount,    
               BudgetStartDate,    
               BudgetEndDate,    
               BudgetFundNumber,    
               ExpenseAmount,    
               IndirectExpenses,    
               DirectExpenses,    
               CashBalance,    
               ReportId,    
               ReportName,    
               REPORTCAT,    
               REPORTSTAT,    
               SubmittedDate,    
               DueDate,    
               StatusDate,    
               CompletedDate,    
			   DaysFromDueToCompleted,    
               SubmittedBy,    
               StatusBy,    
               CompletedBy,    
               PeriodStartDate,    
               PeriodEndDate,    
               DeliverableComments,    
               REPORTCAT_CODE,    
               REPORTSTAT_CODE,    
               AgreementId,    
               InstitutionId,    
               DepartmentId,    
               UnitId,    
               SponsorId,    
               PrincipalInvestigatorId,    
               SubcontractsYN,    
               SubContractorId,    
               SubOutContractorId,
				CASE  
					WHEN ContractSpecialistID='' THEN NULL							  
					ELSE ContractSpecialistID   END as ContractSpecialistID,
			   --ContractSpecialistID,
               ContractSpecialist      
     FROM      @Subcontracts    
     WHERE     (@FundNumber IS NULL OR BudgetFundNumber IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@FundNumber))) AND    
                   
               (@OriginatingSponsor IS NULL OR SecondarySponsor = @OriginatingSponsor ) AND 
               (@ContractManager IS NULL OR ContractSpecialistID = @ContractManager OR    
               (@ContractManager = 'UNASSIGNED' AND (ContractSpecialistID IS NULL OR ContractSpecialistID='') )) AND                      
               -- If user is drilling down, this would be passed...    
               (@Subcontractor IS NULL OR SubContractorName = @SubContractor) AND                       
               (@SubcontractSpecialist IS NULL OR SubmittedBy = @SubcontractSpecialist OR    
               (@SubcontractSpecialist = 'UNASSIGNED' AND SubmittedBy IS NULL ))     
         
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT    
     IF @Error!=0    
     BEGIN    
          RETURN -1    
     END    
    
     -- Return Success    
   RETURN 0    
    
         
END    
go

IF OBJECT_ID('dbo.RptSubContractActivityDetailReport') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptSubContractActivityDetailReport >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptSubContractActivityDetailReport >>>'
go

GRANT EXECUTE ON dbo.RptSubContractActivityDetailReport TO InsightReport,Insight
go
